package com.deng.etl.excel.impl;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Date;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.deng.common.SQLHelper;
import com.deng.etl.excel.IExcelImport;
import com.deng.etl.excel.IExcelRule;
import com.deng.util.DateTools;

public class MenZhenZhuYuanGongZuoLiangImpl extends SQLHelper implements IExcelRule{


	public void importData(Workbook wb, String type_name,String id) {
		try{			
				Connection conn = getConnect();
				
				Sheet sh=wb.getSheet(0);
				int rownum = sh.getRows();
				
				StringBuffer sb = new StringBuffer();
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);
					Cell c2 = sh.getCell(1,i);
					if(sb.indexOf("'"+(getYM1(c1.getContents(),c2.getContents()))+"',") == -1){
						sb.append("'"+(getYM1(c1.getContents(),c2.getContents()))+"',");
					}
				}
				String delSQL = "delete t_org_organization_workload_month" +
						"	where stat_time in ("+sb.toString().substring(0, sb.length()-1)+")";
				updateData(conn, delSQL);//删除stat_Time当天
				
				conn.setAutoCommit(false);
				
				
				String insertSQL = "insert into t_org_organization_workload_month " +
						"	(stat_time,create_time," +
						"org_operate_organization_id,org_operate_organization_name," +
						"ip_out_count," +//（住院）出院人数
//						"ip_in_count" +//没数据（入院人数）  
						"cure_count," +//治疗人数
						"bed_use_count," +//床位使用量
						"bed_open_count," +//床位开放数
						"dispensing_count)" +//发药数量
						"	values(?,'"+DateTools.dtsWithTime(new Date())+"'," +
						"?,?," +
						"?," +
//						"?," +
						"?," +
						"?," +
						"?," +
						"?)";
				PreparedStatement pst =conn.prepareStatement(insertSQL);
				for(int i=2;i<rownum;i++){
					Cell c1 = sh.getCell(0,i);
					if(c1 == null){
						continue;
					}
					if(c1.getContents() == null || "".equals(c1.getContents())){
						continue;
					}
					Cell c2 = sh.getCell(1,i);
					Cell c3 = sh.getCell(2,i);
					Cell c4 = sh.getCell(3,i);
					Cell c5 = sh.getCell(4,i);
					Cell c6 = sh.getCell(5,i);
					Cell c7 = sh.getCell(6,i);
					Cell c8 = sh.getCell(7,i);
					Cell c9 = sh.getCell(8,i);
					
					Date stat_time = DateTools.stdJustDate(getYM1(c1.getContents(),c2.getContents()));
					String org_operate_organization_id = c3.getContents();
					String org_operate_organization_name = c4.getContents();
					Integer ip_out_count = Integer.valueOf(c5.getContents()!=null?c5.getContents().trim():"0");
//					Integer ip_in_count = Integer.valueOf(c5.getContents()!=null?c5.getContents().trim():"0");
					Integer cure_count = Integer.valueOf(c6.getContents()!=null?c6.getContents().trim():"0");
					Integer bed_use_count = Integer.valueOf(c7.getContents()!=null?c7.getContents().trim():"0");
					Integer bed_open_count = Integer.valueOf(c8.getContents()!=null?c8.getContents().trim():"0");
					Integer dispensing_count = Integer.valueOf(c9.getContents()!=null?c9.getContents().trim():"0");
					
					
					pst.setDate(1, new java.sql.Date(stat_time.getTime()));
					pst.setString(2, org_operate_organization_id);
					pst.setString(3, org_operate_organization_name);
					pst.setInt(4, ip_out_count);
					pst.setInt(5, cure_count);
					pst.setInt(6, bed_use_count);
					pst.setInt(7, bed_open_count);
					pst.setInt(8, dispensing_count);
					pst.addBatch();
										
				}
				pst.executeBatch();
				conn.commit();
				pst.close();
				freeConnection(conn);

		}catch(Exception ex){
			ex.printStackTrace();
		}
	}

	private String getYM1(String y,String m){
		return y.trim()+"-"+m.trim()+"-1";
	}
}
